<?php

namespace App\Web\Model;

use App\Facades\UrlCrypt;
use App\Service\ExternalAPI\CaseResumeApi;
use App\Service\DbIndex;
use App\Web\Facades\UserAuth;
use App\Web\Model\Resume as ResumeModel;
use EchoPHP\Facades\Db;
use App\Web\Model\Manager as ManagerModel;
use App\Web\Model\CaseModel as CaseModel;
use EchoPHP\Facades\Log;
use EchoPHP\Facades\Redis;
use Exception;

class CaseResume extends Model {

    protected $tableName = 'CaseResume';
    protected $field = 'id,resumeid,caseid,flowat,flowatstatus,subid,creator,updator,createdate,updatedate';
    private $limit = 10000;


    /**
     * Notes:获取职位中简历信息
     * @param $oprId
     * @param $subId
     * @param $projectId
     * @param array $searchParams
     * @param int $pageNum
     * @param int $pageLimit
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function getCaseResumeInfo($oprId, $subId, $projectId, array $searchParams = [], $pageNum = 1, $pageLimit = 10) {
        list($pageNum, $rowFrom, $rowTo) = $this->paginationParameters($pageNum, $pageLimit);
        $resultBody = [
            'status' => true,
            'message' => '获取成功',
            'totalCount' => 0,
            'pagenum' => $pageNum,
            'engine' => true,
            'keycodes' => '',
            'costLog' => '',
            'listkey' => '',
            'listtoken' => '',
            'eserror' => '',
            'resumelist' => []
        ];

        $searchParams['mode'] = '';
        $caseResumeKeycodes = '';
        //有keycodes，直接分页
        if ($searchParams['keycodes']){
            $searchCaseResumeKeycodes = explode(',', $searchParams['keycodes']);
            $resultBody['totalCount'] = 0;
            $resultBody['engine'] = false;
            //超出最多查询50条
            $caseResumeKeycodes = implode(',', array_slice($searchCaseResumeKeycodes, 0, 50));
        //有简历id直接查询数据库
        }elseif ($searchParams['resumeid']) {
            $baseSearchParams = [
                'resumeid'=>$searchParams['resumeid'],
            ];
            $allResumeList = $this->getCaseResumeList($oprId, $subId, $projectId, $baseSearchParams);
            $searchCaseResumeKeycodes = array_column($allResumeList, 'keycode');
            $resultBody['totalCount'] = count($searchCaseResumeKeycodes);
            $caseResumeKeycodes = implode(',', array_slice($searchCaseResumeKeycodes, $rowFrom, ($rowTo - $rowFrom)));
            $resultBody['keycodes'] = implode(',', $searchCaseResumeKeycodes);
        //查询ES
        } else {
            $searchParams['mode'] = 'ElasticeSearch';
            $searchCaseIds = empty($searchParams['caseid']) ? [] : array_filter(explode(',', $searchParams['caseid']));
            $caseModel = new CaseModel();
            $myCaseList = $caseModel->getCaseListWithProjectId($oprId, $projectId, 'caseid', $searchParams['casestatus'], $searchCaseIds);
            $myCaseIds = array_column($myCaseList, 'caseid');
            $searchParams['caseid'] = implode(',', $myCaseIds);
            if (!empty($searchParams['caseid'])){
                $searchCaseResumeKeycodes = $this->getResumeByElasticSearch($searchParams);
                if ($searchCaseResumeKeycodes) {
                    //es调用失败，只按基础信息查询
                    if (isset($searchCaseResumeKeycodes['eserror'])) {
                        $resultBody['eserror'] = 1;
                        $searchParams['mode'] = '';
                        $baseSearchParams = [
                            'caseid'=>$searchParams['caseid'],
                            'casestatus'=>$searchParams['casestatus'],
                            'includehis'=>$searchParams['includehis'],
                        ];
                        $allResumeList = $this->getCaseResumeList($oprId, $subId, $projectId, $baseSearchParams);
                        $searchCaseResumeKeycodes = array_column($allResumeList, 'keycode');
                        $resultBody['totalCount'] = count($searchCaseResumeKeycodes);
                        $resultBody['keycodes'] = implode(',', $searchCaseResumeKeycodes);
                        $caseResumeKeycodes = implode(',', array_slice($searchCaseResumeKeycodes, $rowFrom, ($rowTo - $rowFrom)));
                    } else {
                        $resultBody['totalCount'] = count($searchCaseResumeKeycodes);
                        $caseResumeKeycodes = implode(',', array_slice($searchCaseResumeKeycodes, $rowFrom, ($rowTo - $rowFrom)));
                        $resultBody['keycodes'] = implode(',', $searchCaseResumeKeycodes);
                    }
                }
            }
        }
        if ($caseResumeKeycodes){
            $resultBody['resumelist'] = $this->getCaseResumeInfoByKeycodes($oprId, $subId, $projectId, $caseResumeKeycodes);
        }

        $resumeidArr = array_column($resultBody['resumelist'], 'resumeid');
        $resultBody['listkey'] = implode(",", $resumeidArr);
        $resultBody['listtoken'] = UrlCrypt::generateToken($resumeidArr);

        //查询简历沟通表头数据信息
        $keyCodes = array_column($resultBody['resumelist'], 'keycode');
        if (!empty($keyCodes)) {
            $contactMap = $this->resumeContactList($keyCodes);
            foreach ($resultBody['resumelist'] as &$item) {
                $item['contactinfo'] = $contactMap[$item['keycode']] ?? [];
            }
        }

        return $resultBody;
    }

    /**
     * 获取职位中简历信息
     * @param $oprId
     * @param $subId
     * @param $caseResumeIds
     * @return mixed
     * @throws Exception
     */
    public function getCaseResumeInfoByIds($oprId, $subId, $projectid, $caseResumeIds) {
        $resumeList = [];
        if (empty($caseResumeIds)) {
            return $resumeList;
        }
        $searchParams = [
            'projectid' => $projectid,
            'caseresumeid' => $caseResumeIds
        ];
        $resumeList = $this->getCaseResumeList($oprId, $subId, $projectid, $searchParams, 0, 1000);
        return $resumeList;
    }

    public function getCaseResumeInfoByKeycodes($oprId, $subId, $projectId, $caseResumeKeycodes) {
        $resumeList = [];
        if (empty($caseResumeKeycodes)) {
            return $resumeList;
        }
        $searchParams = [
            'projectid' => $projectId,
            'caseresumekeycode' => $caseResumeKeycodes
        ];
        $resumeList = $this->getCaseResumeList($oprId, $subId, $projectId, $searchParams, 0, 1000);
        return $resumeList;
    }


    /**
     * Notes:
     * @param $searchParams
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function getResumeByElasticSearch($searchParams) {
        $user = UserAuth::getLoginInfo();
        //我加入过的
        if ($searchParams['searchtype'] == 1){
            $searchParams['creator'] = $user['userid'];
            $searchParams['caseresume_type'] = 1;
        //我操作过的
        }elseif ($searchParams['searchtype'] == 2){
            $searchParams['tagadderid'] = $user['userid'];
            $searchParams['caseresume_type'] = 2;
        //按流程状态查询
        }elseif ($searchParams['searchtype'] == 3){
            $searchParams['caseresume_type'] = $searchParams['includehis'] ? 2 : 1;
        }else{
            $searchParams['caseresume_type'] = 1;
        }

        $searchParams['flowat'] = implode(formatIdsToArr($searchParams['flowat']), ';');
        $searchParams['caseid'] = implode(formatIdsToArr($searchParams['caseid']), ';');
        $searchParams['mobilephone'] = implode(formatIdsToArr($searchParams['resumephone']), ';');
        $searchParams['email'] = implode(formatIdsToArr($searchParams['resumeemail']), ';');
        $searchParams['cname'] = implode(formatIdsToArr($searchParams['resumename']), ';');
        $searchParams['tagadddate_begin'] = $searchParams['starttime'] ? strtotime($searchParams['starttime'] . ' 00:00:00') : '';
        $searchParams['tagadddate_end'] = $searchParams['endtime'] ? strtotime($searchParams['endtime'] . ' 23:59:59') : '';

        $caseResumeApi = new CaseResumeApi();
        $result = $caseResumeApi->getResumeKeycodes($searchParams);
        // ES异常
        if (isset($result['eserror'])) {
            return ['eserror' => 1];
        }

        if (empty($result['data'])) {
            return [];
        }
        $keycodes = explode(';', $result['data']);
        return array_unique($keycodes);
    }

    /**
     * Notes:进度中人才列表
     * @param $searchParams
     * @param $pageNum
     * @param $pageLimit
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function getScheduleByElasticSearch($searchParams, $pageNum, $pageLimit) {
        $resultBody = [
            'status' => true,
            'message' => '获取成功',
            'totalCount' => 0,
            'pagenum' => $pageNum,
            'keycodes' => '',
            'resumelist' => []
        ];
        list($pageNum, $rowFrom, $rowTo) = $this->paginationParameters($pageNum, $pageLimit);
        if (empty($searchParams['keycodes'])){
            //交付方式为全部时循环查询es
            if ($searchParams['chargeway'] == -1){
                $chargeway = (new ProjectReport())->getChargewayByProject($searchParams['projectid']);
                $keycodes = [];
                foreach ($chargeway as $item){
                    $esParams['caseresume_type'] = 2;
                    $esParams['projectid'] = $searchParams['projectid'];
                    $esParams['flowat'] = implode(formatIdsToArr($item['flowat']), ';');
                    $esParams['caseid'] = implode($item['caseids'], ';');
                    $esParams['tagadddate_begin'] = '';
                    $esParams['tagadddate_end'] = '';
                    $caseResumeApi = new CaseResumeApi();
                    $result = $caseResumeApi->getResumeKeycodes($esParams);
                    $tmpKeycodes = array_filter(array_unique(explode(';', $result['data'] ?? '')));
                    $keycodes = array_merge($keycodes, $tmpKeycodes);
                }
            }else{
                $esParams['caseresume_type'] = 2;
                $esParams['tagadderid'] = $searchParams['oprid'] ?? '';
                $esParams['projectid'] = $searchParams['projectid'];
                $esParams['flowat'] = implode(formatIdsToArr($searchParams['flowat']), ';');
                $esParams['caseid'] = implode(formatIdsToArr($searchParams['caseid']), ';');
                $esParams['tagadddate_begin'] = '';
                $esParams['tagadddate_end'] = '';

                $caseResumeApi = new CaseResumeApi();
                $result = $caseResumeApi->getResumeKeycodes($esParams);
                $keycodes = array_filter(array_unique(explode(';', $result['data'] ?? '')));
            }
            if (!$keycodes){
                return $resultBody;
            }
            $resultBody['totalCount'] = count($keycodes);
            $resultBody['keycodes'] = implode(',', $keycodes);
            $caseResumeKeycodes = implode(',', array_slice($keycodes, $rowFrom, ($rowTo - $rowFrom)));
        }else{
            $caseResumeKeycodes = $searchParams['keycodes'];
        }
        $caseResumeParams = [
            'projectid' => $searchParams['projectid'],
            'caseresumekeycode' => $caseResumeKeycodes,
        ];
        $resultBody['resumelist'] = $this->getCaseResumeList(0, $searchParams['subid'], $searchParams['projectid'], $caseResumeParams, 0, 1000);
        return $resultBody;
    }

    /**调用ES返回简历在ES的中的简历ID
     * @param $resumeIds
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function checkResumeInElasticeSearch($resumeIds) {
        $result = ['status' => false, 'message' => '', 'data' => ''];

        if (empty($resumeIds)) {
            $result['message'] = '参数不可为空';
            return $result;
        }

        //需要调用引擎
        $caseResumeApi = new CaseResumeApi();
        //$time1 = microtime(true);
        $result = $caseResumeApi->checkResumeInEs(implode(';', $resumeIds));
        //$time2 = microtime(true);

        return $result;
        //$time3 = microtime(true);
        //Log::error('时间差：取数据库:' . ($time2 - $time1) . ' 处理数据1： ' . ($time3 - $time2) . ' 调用引擎： ' . ($time4 - $time3)
        //. ' 返回结果 ' . ($time5 - $time4));
    }

    /**
     * 获取职位中简历总数
     * @param $oprId
     * @param $subId
     * @param array $searchParams
     * @return mixed
     * @throws Exception
     */
    public function getCaseResumeCount($oprId, $subId, $projectId, array $searchParams = []) {
        $result = $this->getCaseResumeSearch($oprId, $subId, $projectId, $searchParams, true);
        return $result[0]['count'] ?? 0;
    }

    /**
     * Notes:获取职位下简历列表
     * @param $oprId
     * @param $subId
     * @param $projectId
     * @param array $searchParams
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     * @throws Exception
     */
    public function getCaseResumeList($oprId, $subId, $projectId, array $searchParams = [], $rowFrom = 0, $rowTo = 0) {
        $resumeList = $this->getCaseResumeSearch($oprId, $subId, $projectId, $searchParams, false, $rowFrom, $rowTo);
        if (isset($searchParams['mode']) && $searchParams['mode'] == 'ElasticeSearch') {
            return $resumeList;
        }

        $resumeIds = array_column($resumeList, 'resumeid');
        $resumeInfos = $this->getResumeList($resumeIds, $oprId);

        foreach ($resumeList as $key => $item) {
            if (isset($resumeInfos[$item['resumeid']])) {
                $resumeList[$key]['resumeinfo'] = $resumeInfos[$item['resumeid']];
            }
        }

        return $resumeList;
    }

    /**
     * Notes:简历沟通表单记录
     * @param $keyCodes
     * @return array
     */
    public function resumeContactList($keyCodes) {
        $sql = 'SELECT keycode,rc.id,rc.caseid,rc.content as content,rr.content as remark  FROM ResumeContact as rc left join ResumeRemark as rr on rr.id=rc.remarkid WHERE  rc.status=? AND keycode in (' . mergeArrSql($keyCodes) . ')';
        $result = Db::select($sql, array_merge(['01'], $keyCodes));
        $contactMap = [];
        foreach ($result as $item) {
            $item['content'] = json_decode($item['content'], true);
            $contactMap[$item['keycode']] = $item;
        }
        return $contactMap;
    }

    /**
     * 获取简历列表
     * @param $resumeIds
     * @param $oprId
     * @return array
     */
    public function getResumeList($resumeIds, $oprId) {
        $resumeInfoList = $resumeInfos = [];
        if (!empty($resumeIds)) {
            $managerModel = new ManagerModel();
            $fields = 'cname,managerid,userid51job as resumeid,relationaccid as accountid,birthday,gender,area,funtype,workindustry,
            degree,lastupdate,expsalaryfrom,expsalaryto,major,school,company,division,position,workyear,email,mobilephone,
            case when profile is null then 0 else 1 end as isprofile,expectarea,currsalaryfrom,currsalaryto';
            $resumeInfoList = $managerModel->getManagerListByResumeId($resumeIds, $fields);
            $resumeInfoList = $managerModel->chkDownload($oprId, $resumeInfoList);
        }
        foreach ($resumeInfoList as $resumeInfo) {
            $resumeInfos[$resumeInfo['resumeid']] = $resumeInfo;
        }

        return $resumeInfos;
    }

    /**
     * 获取职位中简历
     * @param $oprId
     * @param $subId
     * @param array $searchParams
     * @param bool $searchCount
     * @param int $rowFrom
     * @param int $rowTo
     * @param string $orderBy
     * @return array
     * @throws Exception
     */
    protected function getCaseResumeSearch($oprId, $subId, $projectId, array $searchParams = [], $searchCount = true, $rowFrom = 0, $rowTo = 0) {
        if (!isset($searchParams['mode'])) {
            $searchParams['mode'] = '';
        }
        $searchProjectId = $projectId;
        if ($searchProjectId <= 0) {
            return [];
        }
        //获取当前登录用户能操作的职位信息
        $searchCaseIds = empty($searchParams['caseid']) ? [] : array_filter(explode(',', $searchParams['caseid']));
        $searchCaseStatus = isset($searchParams['casestatus']) && $searchParams['casestatus'] == 'active' ? 'active' : 'all';
        $caseModel = new CaseModel();
        $caseList = $caseModel->getCaseListWithProjectId($oprId, $searchProjectId, 'caseid,casename,status as casestatus', $searchCaseStatus, $searchCaseIds);
        $caseIds = [];
        $caseInfoList = [];
        foreach ($caseList as $caseInfo) {
            $caseIds[] = $caseInfo['caseid'];
            $caseInfoList[$caseInfo['caseid']] = $caseInfo;
        }
        unset($caseList);
        if (empty($caseIds)) {
            return [];
        }

        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];

        $searchResumeId = !empty($searchParams['resumeid']) ? $this->modifySearchArr($searchParams['resumeid']) : '';
        $searchResumeIds = empty($searchResumeId) ? [] : array_filter($searchResumeId);
        $searchCaseResumeIds = empty($searchParams['caseresumeid']) ? [] : array_filter(explode(',', $searchParams['caseresumeid']));
        $searchCaseResumeKeycodes = empty($searchParams['caseresumekeycode']) ? [] : array_filter(explode(',', $searchParams['caseresumekeycode']));
        $searchFlowAt = $searchParams['flowat'] ?? '';

        $searchIncludeHis = isset($searchParams['includehis']) ? boolval($searchParams['includehis']) : false;
        if ($searchIncludeHis && strlen($searchFlowAt) != 2 && strlen($searchFlowAt) != 4) {
            $searchIncludeHis = false;
        }

        //查询历史标签
        if ($searchIncludeHis) {
            $hisSearchParams = [
                'mode' => $searchParams['mode'],
                'searchResumeId' => $searchResumeIds,
                'searchCaseResumeIds' => $searchCaseResumeIds,
                'searchCaseResumeKeycodes' => $searchCaseResumeKeycodes,
                'searchFlowAt' => $searchFlowAt,
            ];

            $result = $this->getHisCaseResumeSearch($oprId, $subId, $searchProjectId, $caseIds, $hisSearchParams, $searchCount, $rowFrom, $rowTo);
            if ($searchCount) {
                return $result;
            }

            if ($searchParams['mode'] != 'ElasticeSearch') {
                for ($i = 0; $i < count($result); $i++) {
                    $result[$i]['casename'] = $caseInfoList[$result[$i]['caseid']]['casename'];
                    $result[$i]['casestatus'] = $caseInfoList[$result[$i]['caseid']]['casestatus'];
                }
            }
            return $result;
        }

        /////////////////////////////////////////////////////////////////////////////
        $where = [];
        $var = [];
        //根据简历ID查询
        if (!empty($searchResumeIds) && is_array($searchResumeIds)) {
            $where[] = 'resumeid IN(' . substr(str_repeat('?,', count($searchResumeIds)), 0, -1) . ')';
            $var = array_merge($var, $searchResumeIds);
        }
        //根据caseresume表主键查询
        if (!empty($searchCaseResumeIds) && is_array($searchCaseResumeIds)) {
            $where[] = 'id IN(' . substr(str_repeat('?,', count($searchCaseResumeIds)), 0, -1) . ')';
            $var = array_merge($var, $searchCaseResumeIds);
        }

        //根据caseresume表keycode查询
        if (!empty($searchCaseResumeKeycodes) && is_array($searchCaseResumeKeycodes)) {
            $where[] = 'keycode IN(' . substr(str_repeat('?,', count($searchCaseResumeKeycodes)), 0, -1) . ')';
            $var = array_merge($var, $searchCaseResumeKeycodes);
        }
        //查询流程
        if (strlen($searchFlowAt) == 4) {
            $where[] = 'flowat=? AND flowatstatus=?';
            $var[] = substr($searchFlowAt, 0, 2);
            $var[] = substr($searchFlowAt, 2, 2);
        } elseif (strlen($searchFlowAt) == 2) {
            $where[] = 'flowat=?';
            $var[] = $searchFlowAt;
        }

        $field = $searchCount ? 'count(1) as count' : null;
        $field = $field ?? 'id,resumeid,caseid,projectid,subid,flowat,flowatstatus,timecontrol,createdate,updator,updatedate,tagadderid,tagaddername,tagadddate,keycode,taghisid,salary';

        if ($searchParams['mode'] == 'ElasticeSearch') {
            $field = 'id,resumeid';
        }

        $caseIdInFlags = substr(str_repeat('?,', count($caseIds)), 0, -1);
        $sql = 'SELECT ' . $field . ' FROM CaseResume_' . $dbIndex['tableid'] . ' WHERE subid=? AND projectid=? AND status=? AND caseid IN(' . $caseIdInFlags . ')';
        if (!empty($where)) {
            $sql .= ' AND ' . implode(' AND ', $where);
        }
        $orderBy = $searchCount ? '' : 'createdate DESC,id DESC';
        if (!empty($orderBy)) {
            $sql .= ' ORDER BY ' . $orderBy;
        }
        $var = array_merge([$subId, $searchProjectId, '01',], $caseIds, $var);
        $result = Db::connect($dbName)->select($sql, $var, $rowFrom, $rowTo);
        if ($searchCount) {
            return $result;
        }

        if ($searchParams['mode'] != 'ElasticeSearch') {
            for ($i = 0; $i < count($result); $i++) {
                $result[$i]['casename'] = $caseInfoList[$result[$i]['caseid']]['casename'];
                $result[$i]['casestatus'] = $caseInfoList[$result[$i]['caseid']]['casestatus'];
            }
        }
        return $result;
    }

    /**
     * 获取历史标签
     * @param $oprId
     * @param $subId
     * @param $searchProjectId
     * @param $caseIds
     * @param array $searchParams
     * @param bool $searchCount
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     * @throws Exception
     */
    protected function getHisCaseResumeSearch($oprId, $subId, $searchProjectId, $caseIds, array $searchParams = [], $searchCount = true, $rowFrom = 0, $rowTo = 0) {
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];
        $where = [];
        $var = [];
        //查询简历id
        if (!empty($searchParams['searchResumeIds']) && is_array($searchParams['searchResumeIds'])) {
            $where[] = 'resumeid IN(' . substr(str_repeat('?,', count($searchParams['searchResumeIds'])), 0, -1) . ')';
            $var = array_merge($var, $searchParams['searchResumeIds']);
        }
        //查询简历keycode
        if (!empty($searchParams['searchCaseResumeKeycodes']) && is_array($searchParams['searchCaseResumeKeycodes'])) {
            $where[] = 'keycode IN(' . substr(str_repeat('?,', count($searchParams['searchCaseResumeKeycodes'])), 0, -1) . ')';
            $var = array_merge($var, $searchParams['searchCaseResumeKeycodes']);
        }
        //查询流程
        $searchFlowAt = $searchParams['searchFlowAt'];
        if (strlen($searchFlowAt) == 4) {
            $where[] = 'flowat=? AND flowatstatus=?';
            $var[] = substr($searchFlowAt, 0, 2);
            $var[] = substr($searchFlowAt, 2, 2);
        } elseif (strlen($searchFlowAt) == 2) {
            $where[] = 'flowat=?';
            $var[] = $searchFlowAt;
        }

        $caseIdInFlags = substr(str_repeat('?,', count($caseIds)), 0, -1);
        $var = array_merge([$subId, $searchProjectId, '01',], $caseIds, $var);

        //仅获取关系ID然后提供给ElasticeSearch
        if ($searchParams['mode'] == 'ElasticeSearch') {
            $var[] = '01';
            $sql = 'SELECT id,resumeid FROM CaseResume_' . $dbIndex['tableid'] . ' WHERE keycode IN(';
            $sql .= 'SELECT distinct keycode FROM CaseResumeTagHis_' . $dbIndex['tableid'] . ' ';
            $sql .= 'WHERE subid=? AND projectid=? AND status=? AND caseid IN(' . $caseIdInFlags . ')';
            if (!empty($where)) {
                $sql .= ' AND ' . implode(' AND ', $where);
            }
            $sql .= ') AND STATUS=? ORDER BY createdate DESC,id DESC';
        } else {
            $field = $searchCount ? 'count(distinct keycode) as count' : null;
            $field = $field ?? 'distinct keycode';
            $sql = 'SELECT ' . $field . ' FROM CaseResumeTagHis_' . $dbIndex['tableid'] . ' WHERE subid=? AND projectid=? AND status=? AND caseid IN(' . $caseIdInFlags . ')';
            if (!empty($where)) {
                $sql .= ' AND ' . implode(' AND ', $where);
            }
            $orderBy = $searchCount ? '' : 'createdate DESC,id DESC';
            if (!empty($orderBy)) {
                $sql .= ' ORDER BY ' . $orderBy;
            }
        }

        $result = Db::connect($dbName)->select($sql, $var, $rowFrom, $rowTo);
        if ($searchCount || empty($result) || $searchParams['mode'] == 'ElasticeSearch') {
            return $result;
        }

        //查询历史标签，需要通过简历id查询当前状态
        $keyCodes = [];
        foreach ($result as $item) {
            $keyCodes[] = $item['keycode'];
        }
        $inFlag = substr(str_repeat('?,', count($keyCodes)), 0, -1);
        $field = 'id,resumeid,caseid,projectid,subid,flowat,flowatstatus,timecontrol,createdate,updator,updatedate,tagadderid,tagaddername,tagadddate,keycode,taghisid';
        $sql = 'SELECT ' . $field . ' FROM CaseResume_' . $dbIndex['tableid'] . ' WHERE keycode IN(' . $inFlag . ') AND status=? ORDER BY createdate DESC,id DESC';
        $var = array_merge($keyCodes, ['01']);
        $result = Db::connect($dbName)->select($sql, $var);
        return $result;
    }

    /**
     * 获取简历标签历史明细
     * @param $subId
     * @param $keyCode
     * @return mixed
     * @throws Exception
     */
    public function getResumeTagHis($subId, $keyCode) {
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];
        $sql = 'select id as casetagid,creator,createdate,flowat,flowatstatus,memo,timecontrol,creatorname as cname,keycode,salary from CaseResumeTagHis_' . $dbIndex['tableid'] . ' where keycode=? and status = ? order by createdate DESC, id desc';
        $result = Db::connect($dbName)->select($sql, [$keyCode, '01']);
        return (new CaseFlow())->rowTrans($result);
    }

    /**
     * 获取在职位中的简历 TODO: 复数caseId与复数resumeId的查询条件组合方式定义不明确
     * @param array $caseIds
     * @param array $resumeIds
     * @param $dbId 数据库id
     * @param $tableId 表id
     * @return array
     */
    public function getResumeInCase(array $caseIds, array $resumeIds, $dbId, $tableId) {
        $inFlag = substr(str_repeat('?,', count($caseIds)), 0, -1);
        $resumeIdInFlag = substr(str_repeat('?,', count($resumeIds)), 0, -1);
        $dbName = 'jyrpo_' . $dbId;
        $realTableName = $this->tableName . '_' . $tableId;
        $sql = 'SELECT id,caseid,resumeid,flowat,flowatstatus,tagaddername,tagadddate,keycode FROM ' . $realTableName . ' WHERE caseid IN(' . $inFlag . ') AND status=? AND resumeid IN(' . $resumeIdInFlag . ')';
        $var = array_merge($caseIds, ['01'], $resumeIds);

        $result = Db::connect($dbName)->select($sql, $var);
        $return = [];
        foreach ($result as $item) {
            $return[$item['caseid']][] = $item;
        }
        return $return;
    }

    /**
     * 根据id获取简历在职位中的数据情况
     * @param $ids
     * @param int $dbId 数据库id
     * @param int $tableId 表id
     * @param null $field
     * @return array
     */
    public function getListByIds($ids, $dbId, $tableId, $field = null) {
        $field = $field ?? $this->field;
        $inFlag = substr(str_repeat('?,', count($ids)), 0, -1);
        $dbName = 'jyrpo_' . $dbId;
        $realTableName = $this->tableName . '_' . $tableId;
        $sql = 'SELECT ' . $field . ' FROM ' . $realTableName . ' WHERE id IN(' . $inFlag . ') AND status=?';
        $var = array_merge($ids, ['01']);

        return Db::connect($dbName)->select($sql, $var);
    }

    /**
     * 根据id获取简历在职位中的数据情况
     * @param $ids
     * @param int $dbId 数据库id
     * @param int $tableId 表id
     * @param null $field
     * @return array
     */
    public function getExportListByIds($ids, $dbId, $tableId, $field = null) {
        $field = $field ?? $this->field;
        $inFlag = substr(str_repeat('?,', count($ids)), 0, -1);
        $dbName = 'jyrpo_' . $dbId;
        $realTableName = $this->tableName . '_' . $tableId;
        $sql = 'SELECT ' . $field . ' FROM ' . $realTableName . ' WHERE id IN(' . $inFlag . ')';
        $var = array_merge($ids);

        return Db::connect($dbName)->select($sql, $var);
    }

    /**
     * Notes:简历对应用户的职位统计
     * @param $resumeId
     * @param $userid
     * @param bool $ismy
     * @return array
     * @throws Exception
     */
    public function getCaseList($resumeId, $userid = 0, $ismy = true, $getProjectInfo = false) {
        $caseFlowInfo = $this->getResumeInCaseByRedis($resumeId);
        if (empty($caseFlowInfo)) {
            return [];
        }
        //根据标签时间DESC排序
        $orderByDatetime = array_column($caseFlowInfo, 'datetime');
        array_multisort($orderByDatetime, SORT_DESC, $caseFlowInfo);

        //取最新500个职位信息
        $caseFlowInfo_Chunk = array_chunk($caseFlowInfo, 500, true);
        $caseFlowInfo_Top = [];
        foreach ($caseFlowInfo_Chunk[0] as $item) {
            $caseFlowInfo_Top[$item['caseid']] = $item;
        }

        $caseModel = new CaseModel();
        $caseIdArr = array_column($caseFlowInfo_Top, 'caseid');
        $field = 'caseid,casename,subid,projectid,salaryfrom,salaryto,area,creator,createdate,duedate,status,hascontact,haschargeway ';
        $caseInfo = $caseModel->getCaseList($caseIdArr, $field);
        if (!$caseInfo) {
            return [];
        }

        $projectId = [];

        //合并职位信息
        foreach ($caseInfo as $item) {
            $caseFlowInfo_Top[$item['caseid']]['casename'] = $item['casename'];
            $caseFlowInfo_Top[$item['caseid']]['salaryfrom'] = $item['salaryfrom'];
            $caseFlowInfo_Top[$item['caseid']]['salaryto'] = $item['salaryto'];
            $caseFlowInfo_Top[$item['caseid']]['creator'] = $item['creator'];
            $caseFlowInfo_Top[$item['caseid']]['createdate'] = $item['createdate'];
            $caseFlowInfo_Top[$item['caseid']]['duedate'] = $item['duedate'];
            $caseFlowInfo_Top[$item['caseid']]['status'] = $item['status'];
            $caseFlowInfo_Top[$item['caseid']]['hascontact'] = $item['hascontact'];
            $caseFlowInfo_Top[$item['caseid']]['haschargeway'] = $item['haschargeway'];

            $projectId[] = $item['projectid'];
        }
        $projectId = array_unique($projectId);
        if($getProjectInfo){
            $projectModel = new Project();
            $projectList = $projectModel->getProjectListById($projectId);
            foreach ($caseFlowInfo_Top as $caseId => $caseInfoItem) {
                $caseFlowInfo_Top[$caseId]['projectname'] = $projectList[$caseInfoItem['projectid']]['projectname'] ?? '';
            }
        }

        $result = [];
        if (!$userid) {
            foreach ($caseFlowInfo_Top as $item) {
                $result[] = $item;
            }
            //翻译字典
            return (new CaseFlow())->rowTrans($result);
        }

        //权限控制
        $oprCaseList = (new CaseOperator())->chkMultiCaseOprRight($userid, array_column($caseFlowInfo_Top, 'caseid'));
        foreach ($caseFlowInfo_Top as $item) {
            if ($ismy && isset($oprCaseList[$item['caseid']])) {
                $result[] = $item;
            } elseif (!$ismy && !isset($oprCaseList[$item['caseid']])) {
                $result[] = $item;
            }
        }
        if (!$result) {
            return [];
        }
        //翻译字典
        return (new CaseFlow())->rowTrans($result);
    }


    /**
     * Notes:团队下此简历在职位中信息
     * @param $resumeId
     * @param $caseId
     * @return array|mixed
     * @throws Exception
     */
    public function getCaseByTeam($resumeId, $caseId) {
        $caseFlowInfo = $this->getResumeInCaseByRedis($resumeId, [$caseId]);
        if (empty($caseFlowInfo)) {
            return [];
        }

        $caseFlowInfo = current($caseFlowInfo);
        $caseModel = new CaseModel();
        $caseId = $caseFlowInfo['caseid'];
        $field = 'caseid,casename,subid,projectid,salaryfrom,salaryto,area,creator,createdate,duedate,status,hascontact,haschargeway ';
        $caseInfo = $caseModel->getCaseList([$caseId], $field);
        if (!$caseInfo) {
            return [];
        }
        $caseFlowInfo = array_merge($caseFlowInfo, $caseInfo[0]);
        $projectModel = new Project();
        $projectInfo = $projectModel->getProjectListById([$caseFlowInfo['projectid']]);
        if (!$projectInfo) {
            return [];
        }
        $projectInfo = array_shift($projectInfo);
        $caseFlowInfo['projectname'] = $projectInfo['projectname'];
        $caseFlowInfo['hascontact'] = '1';
        $caseFlowInfo['haschargeway'] = '1';

        //权限控制

        $right = (new Project())->chkGetRight($caseFlowInfo['projectid']);
        if (!$right) {
            return [];
        }
        //翻译字典
        return (new CaseFlow())->rowTrans([$caseFlowInfo]);
    }

    /**
     * Notes:保存简历加入职位信息到Redis中
     * @param $resumeId
     * @param $caseflowbaseinfo
     *        结构体：['id'=>1, 'caseid' => 1, 'subid' => 1, 'projectid' => 1, 'keycode' => 'aaddddaaaddd', 'flowat' => '04',
     *                'flowatstatus' => '02', 'tagaddername' => '张三', 'datetime' => 数据库操作时间         ]
     * @return bool $result  ===false 说明操作失败，有异常。否则则为成功
     */
    public function setResumeInCaseByRedis($resumeId, $caseFlowBaseInfo = array()) {
        $result = false;
        try {
            $redisObj = Redis::connect('resumeincase');
            $caseFlowBaseInfo = array_filter($caseFlowBaseInfo);

            if (empty($caseFlowBaseInfo)) {
                return $result;
            }

            $tmpInfo = array();
            //同一个简历可以支持批量编辑职位及流程信息
            foreach ($caseFlowBaseInfo as $item) {
                if (empty($item['id']) || empty($item['caseid']) || empty($item['subid']) || empty($item['projectid']) || empty($item['keycode'])
                    || empty($item['flowat']) || empty($item['flowatstatus']) || empty($item['tagaddername'])) {
                    continue;
                }
                if (!isset($item['datetime']) || empty($item['datetime'])) {
                    $item['datetime'] = date('Y-m-d H:i:s');
                }
                $tmpInfo[$item['caseid']] = serialize($item);  // @unserialize($item['extraparam']);
            }

            if (empty($tmpInfo)) {
                return $result;
            }

            $result = $redisObj->HMSET('resumeincase:' . $resumeId, $tmpInfo);
        } catch (Exception $ex) {
            Log::error('保存简历加入职位信息到Redis中出错:[' . $ex->getMessage() . ']', [
                'resumeId' => $resumeId,
                'tmpInfo' => $tmpInfo
            ]);
            return false;
        }

        return $result;
    }

    /**
     * Notes:删除Redis简历职位信息
     * @param $resumeId
     * @param $caseIds
     * @return bool $result  ===false 说明操作失败，有异常。否则则为成功
     */
    public function delResumeInCaseByRedis($resumeId, $caseIds = array()) {
        $result = false;
        try {
            $redisObj = Redis::connect('resumeincase');
            $resumeCase = array('resumeincase:' . $resumeId);
            $caseIds = array_filter($caseIds);

            if (empty($caseIds)) {
                return $result;
            }

            foreach ($caseIds as $item) {
                $resumeCase[] = $item;
                //$result = @$redisObj->hDel($resumeId,$item);   //单条删除
            }

            //批量删除
            $result = call_user_func_array(array($redisObj, 'hdel'), $resumeCase);
        } catch (Exception $ex) {
            Log::error('删除Redis简历职位信息出错:[' . $ex->getMessage() . ']', [
                'resumeId' => $resumeId,
                'caseIds' => $resumeCase,
            ]);
            $result = false;
        }

        return $result;
    }

    /**
     * Notes:获取简历在Redis中加入职位信息
     * @param $resumeId
     * @param $caseIds
     * @return array $result  ===false 说明操作失败，有异常。否则则为成功
     */
    public function getResumeInCaseByRedis($resumeId, $caseIds = array()) {
        $result = array();
        try {
            $caseIds = array_filter($caseIds);
            //$caseIds = [21];
            $redisObj = Redis::connect('resumeincase');

            if (empty($caseIds)) {
                $resultHash = $redisObj->scanHash('resumeincase:' . $resumeId);
                if (!empty($resultHash) && count($resultHash) > 0) {
                    foreach ($resultHash as $item) {
                        if (!empty($item)) {
                            $tmpInfo = @unserialize($item);
                            if (isset($tmpInfo['caseid'])) {
                                $result[$tmpInfo['caseid']] = $tmpInfo;
                            }
                        }
                    }
                }
            } else {
                $resultHash = $redisObj->hMGet('resumeincase:' . $resumeId, $caseIds);
                if (!empty($resultHash) && count($resultHash) > 0) {
                    foreach ($resultHash as $key => $item) {
                        if (!empty($item)) {
                            $tmpInfo = @unserialize($item);
                            if (isset($tmpInfo['caseid'])) {
                                $result[$tmpInfo['caseid']] = $tmpInfo;
                            }
                        }
                    }
                }
            }
        } catch (Exception $ex) {
            Log::error('获取简历在Redis中加入职位信息出错:[' . $ex->getMessage() . ']', [
                'resumeId' => $resumeId,
                'caseIds' => $caseIds,
            ]);
            return false;
        }
        return $result;
    }

    /**
     * Notes:获取简历在Redis中加入职位总数信息
     * @param $resumeId
     * @return $count  ===false 说明操作失败，有异常。否则则为成功
     */
    public function getResumeInCaseCountByRedis($resumeId) {
        $result = 0;
        try {
            $redisObj = Redis::connect('resumeincase');
            $result = $redisObj->hLen('resumeincase:' . $resumeId);
        } catch (Exception $ex) {
            Log::error('获取简历在Redis中加入职位总数信息出错:[' . $ex->getMessage() . ']', [
                'resumeId' => $resumeId
            ]);
            return false;
        }

        return $result;
    }

    /**根据简历ID获取简历职位关系信息
     * @param $resumeList
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function getCaseResumeByResumeId($resumeId, $searchParams = [], $rowFrom = 1, $rowTo = 100) {
        //$pageNum=1,$pageLimit=0,$timeFrom='',$timeTo='
        $caseResumeApi = new CaseResumeApi();

        $searchParams['resumeid'] = $resumeId;

        //$time1 = microtime(true);
        $result = $caseResumeApi->getCaseResumeInfoByEs($searchParams, $rowFrom, $rowTo);
        //$time2 = microtime(true);
        return $result;
    }

    /**通过标签添加人用户ID获取简历职位关系信息
     * @param $resumeList
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function getCaseResumeByTagAdderId($tagAdderId, $searchParams = [], $rowFrom = 1, $rowTo = 100) {
        //$pageNum=1,$pageLimit=0,$timeFrom='',$timeTo='
        $caseResumeApi = new CaseResumeApi();
        //$time1 = microtime(true);

        $searchParams['tagadderid'] = $tagAdderId;
        $searchParams['waitflow'] = 1;
        $result = $caseResumeApi->getCaseResumeInfoByEs($searchParams, $rowFrom, $rowTo);
        //$time2 = microtime(true);

        return $result;
    }

    /**
     * 获取简历沟通记录，附加到传入的数据结构
     * @param array $resumeList 形如 [['resumeid' => 10000000001, 'caseid' => 101], ['resumeid' => 10000000002, 'caseid' => 102]]
     * @return array 补充后的数据结构
     */
    public function getContactContent(array &$resumeList) {
        $keyCodeArr = array_column($resumeList, 'keycode');
        //查询沟通记录表
        $inFlag = substr(str_repeat('? ,', (count($keyCodeArr))), 0, -1);
        $sql = 'select keycode,rc.caseid, rc.resumeid, rc.content, rr.content as remark from ResumeContact as rc left join ResumeRemark as rr on remarkid = rr.id where keycode in (' . $inFlag . ')';
        $result = Db::select($sql, $keyCodeArr);
        if (empty($result)) {
            return $resumeList;
        }
//        $inParams = [];
//        $count = 0;
//        foreach ($resumeList as $resume) {
//            if (!empty($resume['resumeid']) && !empty($resume['caseid'])) {
//                $inParams[] = $resume['caseid'];
//                $inParams[] = $resume['resumeid'];
//                $count++;
//            }
//        }
//        $inPlaceholder = implode(',', array_fill(0, $count, '(?,?)'));
//        $sql = 'select caseid, resumeid, content from ResumeContact where (caseid, resumeid) in (' . $inPlaceholder . ')';
//        $result = Db::select($sql, $inParams);


        //将沟通记录映射成字典，收集所有出现过的字段类型id
        $fieldIdCollection = [];    //字段类型id集合
        $contactDict = [];          //沟通记录字典
        foreach ($result as $row) {
            $contactContent = json_decode($row['content'], true);
            $contactContent = array_merge($contactContent, [['field' => '0000', 'value' => $row['remark']]]);//var_dump($contactContent);exit();
            $fieldIdCollection += @array_flip(array_column($contactContent, 'field'));
            $contactDict[$row['caseid']][$row['resumeid']] = $contactContent;
        }

        //查询字段类型相关信息
        $inPlaceholder = implode(',', array_fill(0, count($fieldIdCollection), '?'));
        $sql = 'select id,fieldname,type,unit from ContactField where id in (' . $inPlaceholder . ')';
        $result = Db::select($sql, array_keys($fieldIdCollection));
        $contactFieldInfo = array_column($result, null, 'id');

        //附加沟通记录信息到原简历数据结构
        foreach ($resumeList as $key => $resume) {
            if (empty($resume['resumeid']) || empty($resume['caseid']) || empty($contactDict[$resume['caseid']][$resume['resumeid']])) continue;
            $contactContent = $contactDict[$resume['caseid']][$resume['resumeid']];
            foreach ($contactContent as $i => $item) {
                $contactContent[$i]['type'] = $contactFieldInfo[$item['field']]['type'] ?? 0;
                $contactContent[$i]['unit'] = $contactFieldInfo[$item['field']]['unit'] ?? '';
                $contactContent[$i]['fieldname'] = $contactFieldInfo[$item['field']]['fieldname'] ?? '评语';
            }
            $resumeList[$key]['contactcontent'] = $contactContent;
        }
//var_dump($resumeList);exit();
        return $resumeList;
    }

    /**
     * Notes:向ES推送流程变动
     * @param $params
     * @return bool
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function syncCaseFlowWithES($params) {
        if (!$params){
            return false;
        }
        $operArr = [];
        foreach ($params as $item){
            $operArr []= implode(chr(16), $item);
        }
        $ESParams['oper_data'] = implode(chr(26), $operArr);
        $caseResumeApi = new CaseResumeApi();
        $caseResumeApi->syncCaseFlow($ESParams);
        return true;
    }

}